/*
   Copyright 2010 jcgarciam [at] gmail [dot] com

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Reflection;
using Cruddy.Persistence.Exceptions;
using Cruddy.Persistence.Attributes;
namespace Cruddy.Persistence
{
    public class PersistenceManager : IDisposable
    {
        private SqlConnection connection;

        private TransactionManager transactionManager;
        private MetadataCache metadataCache;

        public PersistenceManager(string databaseKey)
            : this(databaseKey, IsolationLevel.ReadCommitted)
        {
        }

        public PersistenceManager(string databaseKey, IsolationLevel level)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[databaseKey].ConnectionString;
            connection = new SqlConnection(connectionString);
            transactionManager = new TransactionManager(connection, level);
            metadataCache = new MetadataCache();
        }

        bool OpenConnection()
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    CloseConnection();
                    connection.Open();
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new PersistanceManagerException("There was a problem whyle trying to open the SQLConnection. [" + ex.Message + "]", ex);
            }

        }
        void CloseConnection()
        {
            if (connection != null)
            {
                if (connection.State == ConnectionState.Open)
                {
                    try
                    {
                        transactionManager.RollbackPendingTransactions();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void BeginTransaction()
        {
            transactionManager.BeginTransaction();
        }
        public void CommitTransaction()
        {
            transactionManager.CommitTransaction();
        }
        public void RollbackTransaction()
        {
            transactionManager.RollbackTransaction();
        }

        /// <summary>
        ///  Call the underlying SqlCommand#ExecuteNonQuery which return the amount of affected rows.
        /// 
        ///  Output parameters and FunctionReturn value will get injected in the IParametrizableEntity regardless.
        /// 
        ///  Throw Cruddy.Persistence.Exceptions.PersistanceManagerException if an error ocurred.
        /// </summary>
        /// <param name="parameter">The IParametrizableEntity object</param>
        /// <param name="selector">The StoredProcedureSelector that define which SP to call</param>
        /// <returns>The amount of affected rows</returns>
        public int Execute(IParametrizableEntity parameter, StoredProcedureSelector selector)
        {
            int ret = 0;
            if (OpenConnection())
            {
                try
                {
                    using (SqlCommand procedureCommand = GetProcedureCommandFor(parameter, selector))
                    {
                        ret = procedureCommand.ExecuteNonQuery();
                        injectOutputParam(parameter, procedureCommand);
                    }
                }
                catch (Exception e)
                {
                    throw new PersistanceManagerException("An error ocurred while trying to execute the SP for [" + selector + "]. Error ["
       + e.Message + "]", e);
                }
            }
            return ret;
        }

        /// <summary>
        /// 
        ///  Call the underlying SqlCommand#ExecuteScalar which execute the Query and return the first column of the first rows returned.
        ///  Usefuly when the SP returns back a Generated Id after an INSERT as a 1 column recordset. 
        /// 
        ///  Output parameters and FunctionReturn value will get injected in the IParametrizableEntity regardless.
        /// 
        ///  Throw Cruddy.Persistence.Exceptions.PersistanceManagerException if an error ocurred.
        /// 
        /// </summary>
        /// <param name="parameter">The IParametrizableEntity object</param>
        /// <param name="selector">The StoredProcedureSelector that define which SP to call</param>
        /// <returns>The first column of the first row returned</returns>
        public object ExecuteScalar(IParametrizableEntity parameter, StoredProcedureSelector selector)
        {
            object ret = 0;
            if (OpenConnection())
            {
                try
                {
                    using (SqlCommand procedureCommand = GetProcedureCommandFor(parameter, selector))
                    {
                        ret = procedureCommand.ExecuteScalar();
                        injectOutputParam(parameter, procedureCommand);
                    }

                }
                catch (Exception e)
                {
                    throw new PersistanceManagerException("An error ocurred while trying to execute the SP for [" + selector + "]. Error ["
             + e.Message + "]", e);
                }
            }
            return ret;
        }

        /// <summary>
        /// 
        ///  It uses the [StoredProcedureSelector.SELECT] and call the underlying SqlCommand#ExecuteReader which execute the Query and build a SqlDataReader with the result.
        ///
        ///  Output parameters and FunctionReturn value will get injected in the IParametrizableEntity regardless.
        ///
        ///  Use the DataReaderMapper 'T' Delegate to map each row under the DataReader to the given 'T' param.
        ///  
        ///  Throw Cruddy.Persistence.Exceptions.PersistanceManagerException if an error ocurred.
        /// 
        /// </summary>
        /// <typeparam name="TObject"></typeparam>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        /// <returns>List of TObject</returns>
        public List<TObject> ExecuteAndMap<TObject>(IParametrizableEntity parameter, DataReaderMapper<TObject> mapper)
        {
            List<TObject> resulting = new List<TObject>();
            if (OpenConnection())
            {
                try
                {
                    using (SqlCommand procedureCommand = GetProcedureCommandFor(parameter, StoredProcedureSelector.SELECT))
                    {
                        using (SqlDataReader reader = procedureCommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                TObject target = (TObject)Activator.CreateInstance<TObject>();
                                MappingResult result = mapper(reader, target);
                                if (result == MappingResult.CONTINUE_MAPPING)
                                {
                                    resulting.Add(target);
                                }
                            }
                            reader.Close();
                        }
                        injectOutputParam(parameter, procedureCommand);
                    }
                }
                catch (Exception e)
                {
                    throw new PersistanceManagerException("An error ocurred while trying to execute the SP for [SELECT]. Error ["
                        + e.Message + "]", e);
                }
            }
            return resulting;
        }

        /// <summary>
        /// 
        ///  It uses the [StoredProcedureSelector.SELECT] and call the underlying SqlCommand#ExecuteReader which execute the Query and build a SqlDataReader with the result.
        ///
        ///  Output parameters and FunctionReturn value will get injected in the IParametrizableEntity regardless.
        ///
        ///  Map the resultings columns of the underlying SqlDataReader to each field that have the corresponding [SpColumnResultAttribute] in the TObject,
        ///  returning a List of TObject
        ///  
        ///  Throw Cruddy.Persistence.Exceptions.PersistanceManagerException if an error ocurred.
        /// 
        /// </summary>
        /// <typeparam name="TObject"></typeparam>
        /// <param name="parameter"></param>
        /// <param name="mapper"></param>
        /// <returns>List of TObject</returns>
        public List<TObject> ExecuteAndMap<TObject>(IParametrizableEntity parameter)
        {
            List<TObject> resulting = new List<TObject>();
            if (OpenConnection())
            {
                try
                {
                    using (SqlCommand procedureCommand = GetProcedureCommandFor(parameter, StoredProcedureSelector.SELECT))
                    {
                        using (SqlDataReader reader = procedureCommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                TObject target = (TObject)Activator.CreateInstance<TObject>();
                                
                                MapSqlReaderToInstanceObject(reader, target);

                                resulting.Add(target);
                            }
                            reader.Close();
                        }
                        injectOutputParam(parameter, procedureCommand);
                    }
                }
                catch (Exception e)
                {
                    throw new PersistanceManagerException("An error ocurred while trying to execute the SP for [SELECT]. Error ["
                        + e.Message + "]", e);
                }
            }
            return resulting;
        }

        private void MapSqlReaderToInstanceObject(SqlDataReader reader, Object target)
        {
            FieldInfo[] fi = metadataCache.GetMetadata(target);
            foreach (FieldInfo f in fi)
            {
                SpColumnResultAttribute attr = GetAttribute<SpColumnResultAttribute>(f, typeof(SpColumnResultAttribute));
                if (attr != null)
                {
                    string fname = attr.ColumnName;
                    if (!string.IsNullOrEmpty(fname))
                    {
                        object value = reader[attr.ColumnName];
                        if (!(value is DBNull))
                        {
                            f.SetValue(target, value);
                            break;
                        }
                    }                 
                }
            }
        }
        private void injectOutputParam(IParametrizableEntity parameter, SqlCommand theCommand)
        {
            FieldInfo[] fi = metadataCache.GetMetadata(parameter); // parameter.GetType().GetFields(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance);
            foreach (FieldInfo f in fi)
            {
                object[] attr = f.GetCustomAttributes(typeof(SpParamAttribute), true);
                if (attr != null && attr.Length > 0)
                {
                    SpParamAttribute spAttr = ((SpParamAttribute)attr[0]);
                    switch (spAttr.Direction)
                    {
                        case ParameterDirection.InputOutput:
                        case ParameterDirection.Output:
                        case ParameterDirection.ReturnValue:
                            object value = theCommand.Parameters[spAttr.Name].Value;
                            if (!(value is DBNull))
                            {
                                f.SetValue(parameter, value);
                            }
                            break;
                    }
                }
            }

        }

        private SqlCommand GetProcedureCommandFor(IParametrizableEntity parameter, StoredProcedureSelector selector)
        {
            string spName = GetProcedureName(parameter, selector);

            SqlCommand theCommand = new SqlCommand(spName,
                                                    connection,
                                                    transactionManager.GetCurrentTransaction());

            theCommand.CommandType = CommandType.StoredProcedure;

            List<SqlParameter> parameters = GetParameters(parameter, selector);
            foreach (SqlParameter p in parameters)
            {
                theCommand.Parameters.Add(p);
            }
            return theCommand;
        }

        private string GetProcedureName(IParametrizableEntity parameter, StoredProcedureSelector selector)
        {
            string spName = string.Empty;
            switch (selector)
            {
                case StoredProcedureSelector.INSERT:
                    spName = parameter.GetInsertProcedure();
                    break;
                case StoredProcedureSelector.UPDATE:
                    spName = parameter.GetUpdateProcedure();
                    break;
                case StoredProcedureSelector.DELETE:
                    spName = parameter.GetDeleteProcedure();
                    break;
                case StoredProcedureSelector.SELECT:
                    spName = parameter.GetSelectProcedure();
                    break;
                default:
                    throw new PersistanceManagerException("StoredProcedureSelector is not Defined [" + selector + "]");
            }
            return spName;
        }

        private List<SqlParameter> GetParameters(IParametrizableEntity parameter, StoredProcedureSelector selector)
        {
            List<SqlParameter> result = new List<SqlParameter>();
            {
                FieldInfo[] fi = metadataCache.GetMetadata(parameter);
                foreach (FieldInfo f in fi)
                {
                    switch (selector)
                    {
                        case StoredProcedureSelector.INSERT:
                        case StoredProcedureSelector.UPDATE:
                            GetParametersForInsertOrUpdate(parameter, result, f);
                            break;
                        case StoredProcedureSelector.DELETE:
                            GetParametersForDelete(parameter, result, f);
                            break;
                        case StoredProcedureSelector.SELECT:
                            GetParametersForSelect(parameter, result, f);
                            break;
                    }
                }
            }
            return result;
        }

        private T GetAttribute<T>(FieldInfo f, Type t)
        {
            T ret = default(T);
            object[] attrSpParam = f.GetCustomAttributes(t, true);
            if (attrSpParam != null && attrSpParam.Length > 0)
            {
                ret = (T)attrSpParam[0];
            }
            return ret;
        }

        private void GetParametersForInsertOrUpdate(IParametrizableEntity parameter, List<SqlParameter> result, FieldInfo f)
        {
            SpParamAttribute spAttr = GetAttribute<SpParamAttribute>(f,typeof(SpParamAttribute));
            if (spAttr != null)
            {
                SqlParameter p = new SqlParameter();
                p.ParameterName = spAttr.Name;
                p.Direction = spAttr.Direction;
                object value = f.GetValue(parameter);
                if (value == null)
                    value = DBNull.Value;
                p.Value = value;
                result.Add(p);
            }
        }

        private void GetParametersForDelete(IParametrizableEntity parameter, List<SqlParameter> result, FieldInfo f)
        {            
            SpParamDeleteAttribute spAttr = GetAttribute<SpParamDeleteAttribute>(f,typeof(SpParamDeleteAttribute)) ;
            if (spAttr != null)
            {
                string paramName = spAttr.Name;
                if (string.IsNullOrEmpty(paramName))
                {
                    SpParamAttribute attr = GetAttribute<SpParamAttribute>(f, typeof(SpParamAttribute));
                    if (attr != null)
                    {
                        paramName = attr.Name;
                    }
                }

                if (!string.IsNullOrEmpty(paramName))
                {
                    SqlParameter p = new SqlParameter();
                    p.ParameterName = paramName;
                    object value = f.GetValue(parameter);
                    if (value == null)
                        value = DBNull.Value;
                    p.Value = value;
                    result.Add(p);
                }
            }
        }

        private void GetParametersForSelect(IParametrizableEntity parameter, List<SqlParameter> result, FieldInfo f)
        {
            SpParamSelectAttribute spAttr = GetAttribute<SpParamSelectAttribute>(f, typeof(SpParamSelectAttribute));

            if (spAttr != null)
            {
                string paramName = spAttr.Name;
                if (string.IsNullOrEmpty(paramName))
                {
                    SpParamAttribute attr = GetAttribute<SpParamAttribute>(f, typeof(SpParamAttribute));
                    if (attr != null)
                    {
                        paramName = attr.Name;
                    }
                }

                if (!string.IsNullOrEmpty(paramName))
                {
                    SqlParameter p = new SqlParameter();
                    p.ParameterName = paramName;
                    object value = f.GetValue(parameter);
                    if (value == null)
                        value = DBNull.Value;
                    p.Value = value;
                    result.Add(p);
                }
            }
        }
        #region IDisposable Members
        public void Dispose()
        {
            if (connection != null)
            {
                CloseConnection();
                connection = null;
            }

            metadataCache.Dispose();
            transactionManager.Dispose();
        }
        #endregion
    }

    public enum MappingResult
    {
        CONTINUE_MAPPING, SKIP_MAPPING
    }
    /// <summary>
    ///  Delegate that let you map a Reader to any structure you want. 
    ///  Do not call Read operation on this SqlDataReader, or you may end up skipping records or with
    ///  an Exception.
    /// </summary>
    /// <typeparam name="T">is the Target object to which each row needs to be mapped to</typeparam>
    /// <param name="reader"></param>

    public delegate MappingResult DataReaderMapper<T>(SqlDataReader reader, T target);
}
